package cn.IT.Nf.Test.Dao.Impl;

import cn.IT.Nf.Test.Dao.StaffsDao;
import cn.IT.Nf.Test.Entity.Staffs;
import cn.IT.Nf.Test.Utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class StaffsDaoImpl implements StaffsDao {

    @Override
    public Object[] selectObject(int id) {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sql = "select * from staffs where id = ?";
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            if (rs.next()) {
                return new Object[]{
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getInt("age"),
                        rs.getString("phone"),
                        rs.getString("sta_pos"),
                        rs.getString("add_time"),
                        rs.getString("update_time")
                };
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, ps, conn);
        }
        return null;
    }

    @Override
    public List<Object[]> selectObjectList() {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sql = "select * from staffs";
        List<Object[]> list = new ArrayList<Object[]>();
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Object[]{
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getInt("age"),
                        rs.getString("phone"),
                        rs.getString("sta_pos"),
                        rs.getString("add_time"),
                        rs.getString("update_time")
                });
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, ps, conn);
        }
        return list;
    }

    @Override
    public Staffs selectId(int id) {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sql = "select * from staffs where id=?";
        Staffs staff = null;
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                staff = new Staffs(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getInt("age"),
                        rs.getString("phone"),
                        rs.getString("sta_pos"),
                        rs.getString("add_time"),
                        rs.getString("update_time")
                );
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, ps, conn);
        }
        return staff;
    }

    @Override
    public List<Staffs> select() {
        List<Staffs> list = new ArrayList<Staffs>();
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sql = "select * from staffs";
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Staffs staff = new Staffs(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getInt("age"),
                        rs.getString("phone"),
                        rs.getString("sta_pos"),
                        rs.getString("add_time"),
                        rs.getString("update_time")
                );
                list.add(staff);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, ps, conn);
        }
        return list;
    }

    @Override
    public List<Map<String, Object>> selectAll() {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sql = "select * from staffs";
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                // 遍历每一列
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    Object columnValue = rs.getObject(i);
                    map.put(columnName, columnValue);
                }
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, ps, conn);
        }
        return list;
    }

    @Override
    public Map<String, Object> selectById(int id) {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sql = "select * from staffs where id=?";
        Map<String, Object> map = new HashMap<String, Object>();
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    Object columnValue = rs.getObject(i);
                    map.put(columnName, columnValue);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, ps, conn);
        }
        return map;
    }

    @Override
    public String selectNameById(int id) {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sql = "select name from staffs where id=?";
        String name = null;
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                name = rs.getString("name");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, ps, conn);
        }
        return name;
    }

    @Override
    public long count() {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sql = "select count(*) from staffs";
        long count = 0;
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                count = rs.getLong(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, ps, conn);
        }
        return count;
    }

    @Override
    public List<String> selectName() {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sql = "select name from staffs";
        List<String> list = new ArrayList<String>();
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(rs.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, ps, conn);
        }
        return list;
    }

    @Override
    public int insert(Staffs staffs) {
        int result = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "insert into staffs(name,age,phone,sta_pos,add_time,update_time) values(?,?,?,?,?,?)";
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, staffs.getName());
            ps.setInt(2, staffs.getAge());
            ps.setString(3, staffs.getPhone());
            ps.setString(4, staffs.getSta_pos());
            ps.setString(5, staffs.getAdd_time());
            ps.setString(6, staffs.getUpdate_time());
            result = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(null, ps, conn);
        }
        return result;
    }

    @Override
    public int update(Staffs staffs) {
        int result = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "update staffs set name=?,age=?,phone=?,sta_pos=?,add_time=?,update_time=? where id=?";
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, staffs.getName());
            ps.setInt(2, staffs.getAge());
            ps.setString(3, staffs.getPhone());
            ps.setString(4, staffs.getSta_pos());
            ps.setString(5, staffs.getAdd_time());
            ps.setString(6, staffs.getUpdate_time());
            ps.setInt(7, staffs.getId());
            result = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(null, ps, conn);
        }
        return result;
    }

    @Override
    public int delete(int id) {
        int result = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "delete from staffs where id=?";
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            result = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(null, ps, conn);
        }
        return result;
    }

}
